using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public UsuarioVO getUsuario(int? idUsuario)
		{
			UsuarioVO usuarioVO = null;
			List<UsuarioVO> listUsuarioVOs = getUsuarios(idUsuario, null, null, null);
			if (listUsuarioVOs != null && listUsuarioVOs.Count > 0)
			{
				usuarioVO = listUsuarioVOs[0];
			}
			return usuarioVO;
		}

		public List<UsuarioVO> getUsuarios()
		{
			return getUsuarios(null, null, null, null);
		}

		public List<UsuarioVO> getUsuarios(int? idUsuario)
		{
			return getUsuarios(idUsuario, null, null, null);
		}

        public List<UsuarioVO> getUsuarios(int? idUsuario, int? idPessoa, string user, bool? ativo)
		{
            return getUsuarios(idUsuario, idPessoa, user, ativo, null, null);
        }

		public List<UsuarioVO> getUsuarios(int? idUsuario, int? idPessoa, string user, bool? ativo, int? idFilial, int? idEmpresa)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT usu.idUsuario as usu_idUsuario, usu.user as usu_user, usu.pass as usu_pass, usu.ativo as usu_ativo, usu.idPessoa as usu_idPessoa, pes.pessoa as pes_pessoa, usu.Cadastro as usu_Cadastro, usu.CadastradoPor as usu_CadastradoPor, usu.Edicao as usu_Edicao, usu.EditadoPor as usu_EditadoPor  ");
			query.AppendLine("FROM usuario as usu ");
            query.AppendLine("  LEFT OUTER JOIN pessoa as pes on pes.idPessoa=usu.idPessoa  ");
            query.AppendLine("      LEFT OUTER JOIN filial as fil on fil.idFilial=pes.idFilial  ");
            query.AppendLine("          LEFT OUTER JOIN empresa as emp on emp.idEmpresa=fil.idEmpresa  ");
			if (idUsuario != null)
			{
				query.AppendLine(where + "usu.idUsuario=@idUsuario ");
				where = "AND ";
			}
			if (user != null)
			{
				query.AppendLine(where + "usu.user=@user ");
				where = "AND ";
			}
			if (ativo != null)
			{
				query.AppendLine(where + "usu.ativo=@ativo ");
				where = "AND ";
			}
			if (idPessoa != null)
			{
				query.AppendLine(where + "usu.idPessoa=@idPessoa ");
				where = "AND ";
			}
            if (idFilial != null)
            {
                query.AppendLine(where + "fil.idFilial=@idFilial ");
                where = "AND ";
            }
            if (idEmpresa != null)
            {
                query.AppendLine(where + "emp.idEmpresa=@idEmpresa ");
                where = "AND ";
            }


			query.AppendLine("ORDER BY usu.idUsuario ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idUsuario!= null) cmd.Parameters.Add(getParameter(cmd, "@idUsuario", idUsuario));
			if (user!= null) cmd.Parameters.Add(getParameter(cmd, "@user", user));
			if (ativo!= null) cmd.Parameters.Add(getParameter(cmd, "@ativo", ativo.Value?"S":"N"));
			if (idPessoa!= null) cmd.Parameters.Add(getParameter(cmd, "@idPessoa", idPessoa));
            if (idFilial != null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
            if (idEmpresa != null) cmd.Parameters.Add(getParameter(cmd, "@idEmpresa", idEmpresa));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<UsuarioVO> listUsuario = new List<UsuarioVO>();

				while (reader.Read())
				{
					UsuarioVO usuarioVO = UsuarioVO.CreateVO();
					usuarioVO.idUsuario = reader.GetInt32(reader.GetOrdinal("usu_idUsuario"));
					usuarioVO.user = reader.GetString(reader.GetOrdinal("usu_user"));
					if (!reader.IsDBNull(reader.GetOrdinal("usu_pass")))
                        usuarioVO.pass = DAOT.DecryptPassword( reader.GetString(reader.GetOrdinal("usu_pass")), "melao" );
					usuarioVO.ativo = reader.GetString(reader.GetOrdinal("usu_ativo")).Equals("S");
					usuarioVO.idPessoa = reader.GetInt32(reader.GetOrdinal("usu_idPessoa"));
					if (!reader.IsDBNull(reader.GetOrdinal("pes_pessoa")))
						usuarioVO.idPessoaVO.pessoa = reader.GetString(reader.GetOrdinal("pes_pessoa"));
					usuarioVO.cadastro = reader.GetDateTime(reader.GetOrdinal("usu_Cadastro"));
					usuarioVO.cadastradoPor = reader.GetString(reader.GetOrdinal("usu_CadastradoPor"));
					usuarioVO.edicao = reader.GetDateTime(reader.GetOrdinal("usu_Edicao"));
					usuarioVO.editadoPor = reader.GetString(reader.GetOrdinal("usu_EditadoPor"));


					listUsuario.Add(usuarioVO);
				}
				reader.Close();
				return listUsuario;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setUsuario(UsuarioVO usuarioVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE usuario ");
			query.AppendLine("SET user=@user, pass=@pass, ativo=@ativo, idPessoa=@idPessoa,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idUsuario=@idUsuario ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idUsuario", usuarioVO.idUsuario));
			cmd.Parameters.Add(getParameter(cmd, "@user", usuarioVO.user));
			if (usuarioVO.pass == null)
				cmd.Parameters.Add(getParameter(cmd, "@pass", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@pass", DAOT.EncryptPassword( usuarioVO.pass, "melao")));
			cmd.Parameters.Add(getParameter(cmd, "@ativo", (usuarioVO.ativo==null?" ":(usuarioVO.ativo.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@idPessoa", usuarioVO.idPessoa));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", usuarioVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", usuarioVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", usuarioVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", usuarioVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newUsuario(UsuarioVO usuarioVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO usuario ");
			query.AppendLine("  ( user, pass, ativo, idPessoa, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @user, @pass, @ativo, @idPessoa, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@user", usuarioVO.user));
			if (usuarioVO.pass == null)
				cmd.Parameters.Add(getParameter(cmd, "@pass", System.DBNull.Value));
			else
                cmd.Parameters.Add(getParameter(cmd, "@pass", DAOT.EncryptPassword(usuarioVO.pass, "melao")));
			cmd.Parameters.Add(getParameter(cmd, "@ativo", (usuarioVO.ativo==null?" ":(usuarioVO.ativo.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@idPessoa", usuarioVO.idPessoa));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", usuarioVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", usuarioVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", usuarioVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", usuarioVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('usuario') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delUsuario(UsuarioVO usuarioVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM usuario ");
			query.AppendLine("WHERE idUsuario=@idUsuario ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idUsuario", usuarioVO.idUsuario));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountUsuarioxIdPessoa(int? idPessoa)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idPessoa) as ocorrencias FROM usuario WHERE IdPessoa=@IdPessoa ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdPessoa", idPessoa));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
